USE Lib
GO

---------------------------------------------------------------------------
-- BEGIN MAINTENANCE WRAPPER
----------------------------
IF EXISTS (SELECT * FROM sysobjects WHERE name = 'fn_format') BEGIN
	DROP  FUNCTION  tsql.fn_format
	PRINT '<<< DROP FUNCTION tsql.fn_format - Completed with SUCCESS >>>'
END

GO
----------------------------
-- END MAINTENANCE WRAPPER
---------------------------------------------------------------------------

SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO

/*================================================================================================
Name:			tsql.fn_format
Type:			User defined function 
Owner:					
Description:	Implements standard format function based of functionality in Excel  
Parameters:		@value = value to be formated
				@format = format mask to be used
				@culture = FUTURE use, defaulted to en_us
Dependens on:  	none
Usage:			SELECT tsql.fn_format('1234.56','FIXED','US_EN')

Format options:	GENERAL NUMBER 2345.678
				FIXED = 2345.67
				STANDARD = 2,345.67
				CURRENCY = $2,345.67
				PERCENT = 34.23%
				GENERAL DATE = 7/20/2012 10:57:00 AM
				LONG DATE = Friday, July 20, 2012
				MEDIUM DATE = 20-Jul-2012
				SHORT DATE = 7/20/2012
				LONG TIME = 11:01:34 AM
				MEDIUM TIME = 11:01 AM
				SHORT TIME = 11:01
				TIMESTAMP = 20120801112301
				ZIP OR ZIP5 = 12345
				ZIP9 OR ZIP+4 = 12345-6789
				SSN = 012-34-5678
				PHONE = 1 (617) 234-5678

--------------------------------------------------------------------------------------------------
Project:		http://sqllib.codeplex.com/
Help:			http://sqllib.codeplex.com/wikipage?title=fn_format&referringTitle=Documentation		
Version:		20120822
SrcCtrlApp:		SVN
SrcCtrlPath:	http://sqllib.codeplex.com/SourceControl/list/changesets
--------------------------------------------------------------------------------------------------
History:		04/02/2002 - Adrian E Dudau - Function created
History:		07/20/2012 - Adrian E Dudau - Added culture parameter(placeholder for now) to align with SQL 2012 FORMAT()
History:		08/22/2012 - Adrian E Dudau - Added references to project, SVN and Help
================================================================================================*/

CREATE FUNCTION [tsql].[fn_format] 
	( @value sql_variant
	, @format NVARCHAR(255)  
	, @culture NVARCHAR(255) = 'en-us'
	)
RETURNS NVARCHAR(4000) AS

BEGIN /*FUNCTION*/

	DECLARE @RetVal AS NVARCHAR(4000);
	DECLARE @BaseType AS VARCHAR(255);
	DECLARE @i INT;

	DECLARE @Year AS VARCHAR(4)
	DECLARE @Quarter AS VARCHAR(1)
	DECLARE @MonthNo AS VARCHAR(2)
	DECLARE @MonthName AS VARCHAR(50)
	DECLARE @DayNo AS VARCHAR(2)
	DECLARE @DayName AS VARCHAR(50) 
	DECLARE @Hour AS VARCHAR(2)
	DECLARE @Minute AS VARCHAR(2)
	DECLARE @Second AS VARCHAR(2)
	DECLARE @AmPm AS VARCHAR(2)
	
	-- check if valid parameters passed
	-- if not passthrough input value	
	IF LEN(ISNULL(@format,'')) = 0 
	OR LEN(ISNULL(CAST(@value AS NVARCHAR),'')) = 0 
	BEGIN
		SET @RetVal = CAST(@value AS NVARCHAR(4000))
		GOTO FUNC_EXIT
	END /* IF LEN(ISNULL... */
	
	SET @format = UPPER(LTRIM(RTRIM(ISNULL(@format,''))))
	
	-- for format mask apply
	-- 1. identify delimiter characters:  ,._-/(): {SPACE}|;x xt. and their position
	-- 2. indentify format characters: Y, M, D, H, N, S, MS, AM, PM, 0, 9,#, %, $, (, ), *, >, <, A, &, W, Q, E+, E- , their position and count for each
	-- 3. remove unvanted characters from input string

	SELECT @BaseType = UPPER(CAST(ISNULL(SQL_VARIANT_PROPERTY (@value , 'BaseType'),'') AS VARCHAR));
	--	additional properties available
	--	@Precision, @Scale, @TotalBytes, @Collation, @MaxLength 
	
	SET @RetVal = CAST(ISNULL(@value,'') AS NVARCHAR(4000));

	-- Remove punctuation characters 	
	IF ISDATE(@RetVal) = 0  BEGIN 
		IF @format IN('GENERAL NUMBER','FIXED','STANDARD','CURRENCY','PERCENT') BEGIN
			SET @RetVal = REPLACE(@RetVal,'.',CHAR(254))
			SET @RetVal = REPLACE(@RetVal,'-',CHAR(253))				
		END /* IF @format IN('GENERAL NUMBER'... */
			
		SET @i = 1
	
		WHILE @i < LEN(@RetVal) BEGIN
			IF SUBSTRING(@RetVal,@i,1) IN ('.','-',',','_','=','+','/','\','|',':',';','<','>','?','[',']','{','}','(',')','!','@','#','$','%','^','^','&','*','"','''',' ','~','`')
			BEGIN
				SET @RetVal = REPLACE(@RetVal,SUBSTRING(@RetVal,@i,1),CHAR(255))
			END /* IF SUBSTRING(@RetVal,@i,1) IN (... */
			
			SET @i = @i + 1
		END /* WHILE @i < LEN(@RetVal) */

		SET @RetVal = REPLACE(@RetVal,CHAR(255),'')			
	END /* ISDATE(@RetVal) */ 
	
	IF @format IN('GENERAL NUMBER','FIXED','STANDARD','CURRENCY','PERCENT') BEGIN
		SET @RetVal = REPLACE(@RetVal,CHAR(254),'.')
		SET @RetVal = REPLACE(@RetVal,CHAR(253),'-')				

		IF @format = 'GENERAL NUMBER' BEGIN
			GOTO FUNC_EXIT
		END
		
		IF @format = 'FIXED' BEGIN 
			SET @RetVal = ROUND(@RetVal,2);	
		END
		
		
		IF @format = 'STANDARD' BEGIN
			SET @RetVal = CAST(CONVERT(VARCHAR, CAST(@RetVal AS MONEY), 1) AS NVARCHAR(4000))	
		END

		IF @format = 'CURRENCY' BEGIN
			SET @RetVal = '$' + CAST(CONVERT(VARCHAR, CAST(@RetVal AS MONEY), 1) AS NVARCHAR(4000))	
		END
		
		IF @format = 'PERCENT' BEGIN
			SET @RetVal = CAST(CONVERT(VARCHAR, CAST(@RetVal AS MONEY), 1) AS NVARCHAR(4000)) + '%'	
		END
	
		GOTO FUNC_EXIT
	END /* IF ISNUMERIC(@RetVal) = 1 */

	--IF @BaseType IN('DATE','DATETIME','DATETIME2','DATETIMEOFFSET','TIME','SMALLDATETIME') 
	IF @format IN ('GENERAL DATE','LONG DATE','MEDIUM DATE','SHORT DATE','LONG TIME','MEDIUM TIME','SHORT TIME','TIMESTAMP') BEGIN 
		IF ISDATE(@RetVal) = 0 BEGIN
			SET @RetVal = NULL
		END 
		
		ELSE BEGIN 
			SET @Year = CAST(YEAR(CAST(@value AS DATETIME)) AS VARCHAR(4))
			SET @Quarter = CAST(DATEPART(QUARTER,CAST(@value AS DATETIME)) AS VARCHAR(1))
			SET @MonthNo = CAST(MONTH(CAST(@value AS DATETIME)) AS VARCHAR(2))
			SET @MonthName = DATENAME(MONTH,CAST(@value AS DATETIME))
			SET @DayNo = CAST(DAY(CAST(@value AS DATETIME)) AS VARCHAR(2))
			SET @DayName = DATENAME(WEEKDAY, CAST(@value AS DATETIME))
			SET @Hour = CAST(DATEPART(HOUR,CAST(@value AS DATETIME)) AS VARCHAR(2))
			SET @Minute = RIGHT('00' + CAST(DATEPART(MINUTE,CAST(@value AS DATETIME)) AS VARCHAR(2)),2)
			SET @Second = RIGHT('00' + CAST(DATEPART(SECOND, CAST(@value AS DATETIME)) AS VARCHAR(2)),2)
			SELECT @AmPm = RIGHT(CAST(CAST(@value AS DATETIME) AS VARCHAR(20)),2) 
						
			
			IF @format = 'GENERAL DATE' /* 7/20/2012 10:57:00 AM */
				SET @RetVal = @MonthNo + '/' + @DayNo + '/' + @Year + ' ' + @Hour + ':' + @Minute + ':' + @Second + ' ' + @AmPm ;
			IF @format = 'LONG DATE' /* Friday, July 20, 2012 */ 
				SET @RetVal = @DayName + ', ' + @MonthName + ' ' + @MonthNo + ', ' + @Year; 
			IF @format = 'MEDIUM DATE' /* 20-Jul-2012 */ 
				SET @RetVal = @MonthNo + '-' + LEFT(@MonthName, 3) + '-' + @Year;
			IF @format = 'SHORT DATE' /* 7/20/2012 */ 
				SET @RetVal = @MonthNo + '/' + @DayNo + '/' + @Year;
			IF @format = 'LONG TIME' /* 11:01:24 AM */ 
				SET @RetVal = @Hour + ':' + @Minute + ':' + @Second + ' ' + @AmPm;
			IF @format = 'MEDIUM TIME' /* 11:01 AM */ 
				SET @RetVal = @Hour + ':' + @Minute + ' ' + @AmPm;
			IF @format = 'SHORT TIME' /* 11:01 */ 
				SET @RetVal = @Hour + ':' + @Minute;
			IF @format = 'TIMESTAMP' /* YYYYMMDDHHNNSS */ 
				SET @RetVal = @Year + RIGHT('00' + @MonthNo,2) + RIGHT('00' + @DayNo,2) + RIGHT('00' + @Hour,2) + @Minute + @Second;
				
			GOTO FUNC_EXIT	
		END

	END /* IF @format IN ('GENERAL DATE'... */


	--IF @BaseType IN('CHAR','NCHAR','NVARCHAR','VARCHAR') BEGIN
		IF @format IN('ZIP','ZIP5') BEGIN
			IF ISNUMERIC(@RetVal) = 1 
			AND LEN(@RetVal) <= 5 BEGIN
				SET @RetVal = RIGHT('00000' + @RetVal,5);
			END /* IF ISNUMERIC(@RetVal) */
		END /* IF @format IN('ZIP','ZIP5') */

		IF REPLACE(@format, SPACE(1),'') IN('ZIP9','ZIP+4') BEGIN
			IF ISNUMERIC(@RetVal) = 1 BEGIN 
				IF LEN(@RetVal) < 5 BEGIN 
					SET @RetVal = RIGHT('00000' + @RetVal,5) + '-0000' ;
				END /* IF LEN(@RetVal) < 5 */
			END /* IF ISNUMERIC(@RetVal) */
			 
			IF LEN(@RetVal) BETWEEN 5 AND 9 BEGIN
				SET @RetVal = LEFT(@RetVal + '0000',5) + '-' + SUBSTRING(@RetVal + '0000',6,4);
			END /* IF LEN(@RetVal) BETWEEN... */
		END /* IF REPLACE(@format... */

		IF @format = 'PHONE' BEGIN			
			IF LEN(@RetVal) = 7 BEGIN
				SET @RetVal = LEFT(@RetVal,3) + '-' + RIGHT(@RetVal,4);
			END

			IF LEN(@RetVal) = 10 BEGIN
				SET @RetVal = '(' + LEFT(@RetVal,3) + ')' + SUBSTRING(@RetVal,4,3) + '-' + RIGHT(@RetVal,4);
			END
		
			IF LEN(@RetVal) = 11 
			AND LEFT(@RetVal,1) = '1' BEGIN
				SET @RetVal = LEFT(@RetVal,1) + ' (' + SUBSTRING(@RetVal,2,3) + ') ' + SUBSTRING(@RetVal,5,3) + '-' + RIGHT(@RetVal,4);
			END

		END /* IF @format = 'PHONE' */
		
		IF @format = 'SSN' 
		AND LEN(@RetVal) = 9 BEGIN
			SET @RetVal = LEFT(@RetVal,3) + '-' + SUBSTRING(@RetVal,4,2) + '-' + SUBSTRING(@RetVal,6,4);	
		END	/* IF @format = 'SSN' */		

		GOTO FUNC_EXIT	
	--END /* IF @BaseType IN('CHAR'... */
	
FUNC_EXIT:
	RETURN @RetVal		
END /*FUNCTION*/

GO

---------------------------------------------------------------------------
-- BEGIN MAINTENANCE WRAPPER
-- Verify completion
----------------------------

IF EXISTS(SELECT * FROM sysobjects WHERE name = 'fn_format') BEGIN 
	PRINT '<<< CREATE FUNCTION tsql.fn_format - Completed with SUCCESS >>>'
END

ELSE BEGIN 
	PRINT '<<< CREATE FUNCTION tsql.fn_format - Completed with ERROR >>>'
END


GO
----------------------------
-- END MAINTENANCE WRAPPER
---------------------------------------------------------------------------

-- GRANT SELECT TO PUBLIC will be used at Db level



